# -*- codeing = utf-8 -*-
# @Time: 2021/3/11 19:53
# @Author: Foxhuty
# @File: score_data_analysis.py
# @Software: PyCharm
import pandas as pd
import numpy as np
import time
import os


class ExamRoom(object):
    def __init__(self, path):
        self.df_arts = pd.read_excel(path, sheet_name='文科', index_col='序号',
                                     dtype={'班级': str, '考号': str, '序号': str})
        self.df_science = pd.read_excel(path, sheet_name='理科', index_col='序号',
                                        dtype={'班级': str, '考号': str, '序号': str})

    def exam_room_info(self):
        """
        计算生成文理科各考室学生名单
        :return:
        """
        self.df_arts.sort_values(by='总分', ascending=False, inplace=True)
        self.df_science.sort_values(by='总分', ascending=False, inplace=True)
        if len(self.df_arts) % 30 != 0:
            room_numbers = [f'文科第{str(i + 1)}考室' for i in list(range(len(self.df_arts) // 30 + 1))]
        else:
            room_numbers = [f'文科第{str(i + 1)}考室' for i in list(range(len(self.df_arts) // 30))]
        if len(self.df_science) % 30 != 0:
            room_numbers_science = [f'理科第{str(i + 1)}考室' for i in list(range(len(self.df_science) // 30 + 1))]
        else:
            room_numbers_science = [f'理科第{str(i + 1)}考室' for i in list(range(len(self.df_science) // 30))]
        print(room_numbers)
        print(room_numbers_science)
        df_arts = self.df_arts.copy()
        df_science = self.df_science.copy()
        df_arts['考室号'] = ''
        df_arts['座位号'] = ''
        df_arts = df_arts.loc[:, ['班级', '姓名', '考号', '考室号', '座位号']]
        df_arts.reset_index(drop=True, inplace=True)
        df_science['考室号'] = ''
        df_science['座位号'] = ''
        df_science = df_science.loc[:, ['班级', '姓名', '考号', '考室号', '座位号']]
        df_science.reset_index(drop=True, inplace=True)

        df_room_students = []
        df_room_students_science = []
        for idx, room_number in enumerate(room_numbers):
            begin = idx * 30
            end = begin + 30
            df_room_student = df_arts.iloc[begin:end]
            df_room_students.append((idx, room_number, df_room_student))
            # print(df_room_students)
        writer = pd.ExcelWriter(r'D:\成绩统计结果\文理科考室学生名单.xlsx')
        for idx, room_number, df_room_student in df_room_students:
            for i in df_room_student.index:
                df_room_student['考室号'].at[i] = room_number
                df_room_student['座位号'].at[i] = i + 1 if i < 30 else i - idx * 30 + 1
                df_room_student.to_excel(writer, sheet_name=room_number, index=False)
        for idx, room_number in enumerate(room_numbers_science):
            begin = idx * 30
            end = begin + 30
            df_room_student_science = df_science.iloc[begin:end]
            df_room_students_science.append((idx, room_number, df_room_student_science))
        for idx, room_number, df_room_student_science in df_room_students_science:
            for i in df_room_student_science.index:
                df_room_student_science['考室号'].at[i] = room_number
                df_room_student_science['座位号'].at[i] = i + 1 if i < 30 else i - idx * 30 + 1
                df_room_student_science.to_excel(writer, sheet_name=room_number, index=False)
        df_arts_general = df_arts.sort_values(by=['班级', '考室号'], ascending=[True, True])
        df_science_general = df_science.sort_values(by=['班级', '考室号'], ascending=[True, True])
        df_arts_general.to_excel(writer, sheet_name='文科', index=False)
        df_science_general.to_excel(writer, sheet_name='理科', index=False)
        # 计算考生座签
        df_seat_arts = df_arts
        df_seat_science = df_science
        for i in df_seat_arts.index:
            df_seat_arts.loc[i + 0.5] = ['班级', '姓名', '考号', '考室号', '座位号']
            # df2.to_excel(f'{df2.iloc[0,0]}.xlsx',index=False)
        df_seat_arts.sort_index(inplace=True, ignore_index=True)
        for i in df_seat_science.index:
            df_seat_science.loc[i + 0.5] = ['班级', '姓名', '考号', '考室号', '座位号']
        df_seat_science.sort_index(inplace=True, ignore_index=True)
        df_seat_arts.to_excel(writer, sheet_name='文科座签', index=False)
        df_seat_science.to_excel(writer, sheet_name='理科座签', index=False)

        writer.close()
        print('successfully done')


class ContrastScores(object):
    def __init__(self, path_this, path_last):
        self.path_this = path_this
        self.path_last = path_last

    def get_df_contrast(self, exam1=None, exam2=None):
        """
        计算取得文科与理科在两次考试中的成绩对比，输出为excel表格
        :param exam1: 本次考试名称，如期末，半期，一诊，二诊等
        :param exam2: 作为对比的上一次考试名称，如期末，半期，一诊，二诊等
        :return: 生成excel电子表格，无返回值
        """
        df_arts_this = pd.read_excel(self.path_this, sheet_name='文科', dtype={'班级': str, '考生号': str, '考号': str})
        df_arts_this['名次'] = df_arts_this['总分'].rank(method='min', ascending=False)
        df_arts_last = pd.read_excel(self.path_last, sheet_name='文科', dtype={'班级': str, '考生号': str, '考号': str})
        df_arts_last['名次'] = df_arts_last['总分'].rank(method='min', ascending=False)
        df_arts_last = df_arts_last.loc[:, ['姓名', '总分', '名次']]
        df_contrast = df_arts_this.merge(df_arts_last, on='姓名', how='left')
        df_contrast['变化'] = df_contrast['名次_y'] - df_contrast['名次_x']
        df_contrast.rename(columns={'总分_x': exam1 + '总分', '名次_x': exam1 + '名次',
                                    '总分_y': exam2 + '总分', '名次_y': exam2 + '名次'}, inplace=True)

        class_names = df_contrast['班级'].unique()

        df_science_this = pd.read_excel(self.path_this, sheet_name='理科', dtype={'班级': str, '考生号': str, '考号': str})
        df_science_this['名次'] = df_science_this['总分'].rank(method='min', ascending=False)
        df_science_last = pd.read_excel(self.path_last, sheet_name='理科', dtype={'班级': str, '考生号': str, '考号': str})
        df_science_last['名次'] = df_science_last['总分'].rank(method='min', ascending=False)
        df_science_last = df_science_last.loc[:, ['姓名', '总分', '名次']]
        df_science_contrast = df_science_this.merge(df_science_last, on='姓名', how='left')
        df_science_contrast['变化'] = df_science_contrast['名次_y'] - df_science_contrast['名次_x']
        df_science_contrast.rename(columns={'总分_x': exam1 + '总分', '名次_x': exam1 + '名次',
                                            '总分_y': exam2 + '总分', '名次_y': exam2 + '名次'}, inplace=True)
        class_names_science = df_science_contrast['班级'].unique()
        writer = pd.ExcelWriter(r'D:\成绩统计结果\与上次考试成绩对比表.xlsx')
        for i in class_names:
            class_name = df_contrast[df_contrast['班级'] == i].reset_index(drop=True)
            class_name['序号'] = [k + 1 for k in class_name.index]
            class_name.to_excel(writer, sheet_name=i, index=False)
        for i in class_names_science:
            class_name_science = df_science_contrast[df_science_contrast['班级'] == i].reset_index(drop=True)
            class_name_science['序号'] = [k + 1 for k in class_name_science.index]
            class_name_science.to_excel(writer, sheet_name=i, index=False)

        df_contrast.to_excel(writer, sheet_name='文科对比表', index=False)
        df_science_contrast.to_excel(writer, sheet_name='理科对比表', index=False)
        writer.close()


class ScoreAnalysis(object):
    arts_scores = []
    science_scores = []

    def __init__(self, path):
        self.df_arts = pd.read_excel(path, sheet_name='文科', index_col='序号',
                                     dtype={
                                         '班级': 'str',
                                         '序号': 'str',
                                         '名次': 'str',
                                         '考号': 'str',
                                         '考生号': 'str'

                                     }
                                     )
        self.df_science = pd.read_excel(path, sheet_name='理科', index_col='序号',
                                        dtype={
                                            '班级': 'str',
                                            '序号': 'str',
                                            '名次': 'str',
                                            '考号': 'str',
                                            '考生号': 'str'

                                        }
                                        )

    def get_av(self):
        """
        计算各科平均分
        :return: 文科，理科各班各科平均分
        """
        av_class_arts = self.df_arts.groupby(['班级'])[['语文', '数学', '英语', '政治', '历史', '地理', '总分']].mean()
        av_general_arts = self.df_arts[['语文', '数学', '英语', '政治', '历史', '地理', '总分']].apply(np.nanmean, axis=0)
        av_general_arts.name = '文科平均分'
        av_arts = av_class_arts.append(av_general_arts)
        av_class_science = self.df_science.groupby(['班级'])[['语文', '数学', '英语', '物理', '化学', '生物', '总分']].mean()
        av_general_science = self.df_science[['语文', '数学', '英语', '物理', '化学', '生物', '总分']].apply(np.nanmean, axis=0)
        av_general_science.name = '理科平均分'
        av_science = av_class_science.append(av_general_science)
        return av_arts, av_science

    def get_goodscores_arts(self, goodtotal):
        """
        计算文科各科有效分
        goodtotal:划线总分，高线，中线，低线
        """
        goodscoredata = self.df_arts.loc[self.df_arts['总分'] >= goodtotal]
        chnav = goodscoredata['语文'].mean()
        mathav = goodscoredata['数学'].mean()
        engav = goodscoredata['英语'].mean()
        polav = goodscoredata['政治'].mean()
        hisav = goodscoredata['历史'].mean()
        geoav = goodscoredata['地理'].mean()
        totalav = goodscoredata['总分'].mean()
        factor = goodtotal / totalav
        chn = round(chnav * factor)
        math = round(mathav * factor)
        eng = round(engav * factor)
        pol = round(polav * factor)
        his = round(hisav * factor)
        geo = round(geoav * factor)
        if (chn + math + eng + pol + his + geo) > goodtotal:
            math -= 1
        if (chn + math + eng + pol + his + geo) < goodtotal:
            eng += 1

        return chn, math, eng, pol, his, geo, goodtotal

    def get_goodscores_science(self, goodtotal):
        """
        计算理科各科有效分
        goodtotal:划线总分，高线，中线，低线
        """
        goodscoredata = self.df_science.loc[self.df_science['总分'] >= goodtotal]
        chnav = goodscoredata['语文'].mean()
        mathav = goodscoredata['数学'].mean()
        engav = goodscoredata['英语'].mean()
        physav = goodscoredata['物理'].mean()
        chemav = goodscoredata['化学'].mean()
        bioav = goodscoredata['生物'].mean()
        totalav = goodscoredata['总分'].mean()
        factor = goodtotal / totalav
        chn = int(round(chnav * factor))
        math = int(round(mathav * factor))
        eng = int(round(engav * factor))
        phys = int(round(physav * factor))
        chem = int(round(chemav * factor))
        bio = int(round(bioav * factor))

        return chn, math, eng, phys, chem, bio, goodtotal

    def goodscore_arts(self, chn, math, eng, pol, his, geo, total):
        """
        计算文科各科各班单有效和双有效人数
        """
        single_chn_arts = self.df_arts[self.df_arts['语文'] >= chn].groupby(['班级'])['语文'].count()
        single_math_arts = self.df_arts[self.df_arts['数学'] >= math].groupby(['班级'])['数学'].count()
        single_eng_arts = self.df_arts[self.df_arts['英语'] >= eng].groupby(['班级'])['英语'].count()
        single_pol_arts = self.df_arts[self.df_arts['政治'] >= pol].groupby(['班级'])['政治'].count()
        single_his_arts = self.df_arts[self.df_arts['历史'] >= his].groupby(['班级'])['历史'].count()
        single_geo_arts = self.df_arts[self.df_arts['地理'] >= geo].groupby(['班级'])['地理'].count()
        single_total_arts = self.df_arts[self.df_arts['总分'] >= total].groupby(['班级'])['总分'].count()

        name_num = self.df_arts.groupby(['班级'])['姓名'].count()
        name_num.name = '参考人数'

        df2 = self.df_arts[self.df_arts['总分'] >= total]
        double_chn_arts = df2[df2['语文'] >= chn].groupby(['班级'])['语文'].count()
        double_math_arts = df2[df2['数学'] >= math].groupby(['班级'])['数学'].count()
        double_eng_arts = df2[df2['英语'] >= eng].groupby(['班级'])['英语'].count()
        double_pol_arts = df2[df2['政治'] >= pol].groupby(['班级'])['政治'].count()
        double_his_arts = df2[df2['历史'] >= his].groupby(['班级'])['历史'].count()
        double_geo_arts = df2[df2['地理'] >= geo].groupby(['班级'])['地理'].count()
        double_total_arts = df2[df2['总分'] >= total].groupby(['班级'])['总分'].count()

        goodscore_dict = {'参考人数': ' ', '语文': chn, '数学': math, '英语': eng, '政治': pol, '历史': his, '地理': geo, '总分': total}
        goodscore_df = pd.DataFrame(goodscore_dict, index=['有效分数'])

        result_single = pd.concat([name_num, single_chn_arts, single_math_arts, single_eng_arts,
                                   single_pol_arts, single_his_arts, single_geo_arts, single_total_arts],
                                  axis=1)
        result_double = pd.concat(
            [name_num, double_chn_arts, double_math_arts, double_eng_arts,
             double_pol_arts, double_his_arts, double_geo_arts, double_total_arts], axis=1)

        result_single.loc['文科'] = [result_single['参考人数'].sum(),
                                   result_single['语文'].sum(),
                                   result_single['数学'].sum(),
                                   result_single['英语'].sum(),
                                   result_single['政治'].sum(),
                                   result_single['历史'].sum(),
                                   result_single['地理'].sum(),
                                   result_single['总分'].sum()
                                   ]
        result_single['上线率'] = result_single['总分'] / result_single['参考人数']
        result_single['上线率'] = result_single['上线率'].apply(lambda x: format(x, '.2%'))
        result_double.loc['文科'] = [result_double['参考人数'].sum(),
                                   result_double['语文'].sum(),
                                   result_double['数学'].sum(),
                                   result_double['英语'].sum(),
                                   result_double['政治'].sum(),
                                   result_double['历史'].sum(),
                                   result_double['地理'].sum(),
                                   result_double['总分'].sum()]
        unmatched_dict = {'参考人数': name_num, '语文': single_total_arts - double_chn_arts,
                          '数学': single_total_arts - double_math_arts, '英语': single_total_arts - double_eng_arts,
                          '政治': single_total_arts - double_pol_arts, '历史': single_total_arts - double_his_arts,
                          '地理': single_total_arts - double_geo_arts,
                          '总分': single_total_arts - double_total_arts}
        unmatched_df = pd.DataFrame(unmatched_dict)
        unmatched_df.loc['共计'] = [unmatched_df['参考人数'].sum(),
                                  unmatched_df['语文'].sum(),
                                  unmatched_df['数学'].sum(),
                                  unmatched_df['英语'].sum(),
                                  unmatched_df['政治'].sum(),
                                  unmatched_df['历史'].sum(),
                                  unmatched_df['地理'].sum(),
                                  unmatched_df['总分'].sum()]
        result_final_arts = pd.concat([goodscore_df, result_single, result_double, unmatched_df], axis=0,
                                      keys=['有效分数', '单有效', '双有效', '错位数'])

        df_chn = df2.loc[:, ['班级', '姓名', '语文']].loc[df2['语文'] < chn].sort_values(by=['班级', '语文'],
                                                                                 ascending=[True, False]).reset_index(
            drop=True)
        df_math = df2.loc[:, ['班级', '姓名', '数学']].loc[df2['数学'] < math].sort_values(by=['班级', '数学'],
                                                                                   ascending=[True, False]).reset_index(
            drop=True)
        df_eng = df2.loc[:, ['班级', '姓名', '英语']].loc[df2['英语'] < eng].sort_values(by=['班级', '英语'],
                                                                                 ascending=[True, False]).reset_index(
            drop=True)
        df_pol = df2.loc[:, ['班级', '姓名', '政治']].loc[df2['政治'] < pol].sort_values(by=['班级', '政治'],
                                                                                 ascending=[True, False]).reset_index(
            drop=True)
        df_his = df2.loc[:, ['班级', '姓名', '历史']].loc[df2['历史'] < his].sort_values(by=['班级', '历史'],
                                                                                 ascending=[True, False]).reset_index(
            drop=True)
        df_geo = df2.loc[:, ['班级', '姓名', '地理']].loc[df2['地理'] < geo].sort_values(by=['班级', '地理'],
                                                                                 ascending=[True, False]).reset_index(
            drop=True)
        unmatched_arts = pd.concat([df_chn, df_math, df_eng, df_pol, df_his, df_geo], axis=1)
        shoot_dict = {'语文': result_double['语文'] / result_single['语文'],
                      '数学': result_double['数学'] / result_single['数学'],
                      '英语': result_double['英语'] / result_single['英语'],
                      '政治': result_double['政治'] / result_single['政治'],
                      '历史': result_double['历史'] / result_single['历史'],
                      '地理': result_double['地理'] / result_single['地理'],
                      '上线数80%': result_single['总分'] * 0.8}
        shoot_df = pd.DataFrame(shoot_dict)
        contribution_dict = {'语文': result_double['语文'] / result_double['总分'],
                             '数学': result_double['数学'] / result_double['总分'],
                             '英语': result_double['英语'] / result_double['总分'],
                             '政治': result_double['政治'] / result_double['总分'],
                             '历史': result_double['历史'] / result_double['总分'],
                             '地理': result_double['地理'] / result_double['总分'],
                             '上线数80%': result_double['总分'] * 0.8}
        contribution_df = pd.DataFrame(contribution_dict)
        result_single.fillna(0, inplace=True)
        result_double.fillna(0, inplace=True)
        shoot_df.fillna(0, inplace=True)
        contribution_df.fillna(0, inplace=True)
        grade = pd.DataFrame(columns=['语文', '数学', '英语', '政治', '历史', '地理'], index=shoot_df.index)

        def grade_assess(subject):

            for i in shoot_df.index:
                if result_single['总分'].at[i] != 0:
                    if (result_single[subject].at[i]) >= (result_single['总分'].at[i]) * 0.8:
                        if (contribution_df[subject].at[i] >= 0.7) & (shoot_df[subject].at[i] >= 0.6):
                            grade[subject].at[i] = 'A'
                        elif (contribution_df[subject].at[i] >= 0.7) & (shoot_df[subject].at[i] < 0.6):
                            grade[subject].at[i] = 'B'
                        elif (contribution_df[subject].at[i] < 0.7) & (shoot_df[subject].at[i] >= 0.6):
                            grade[subject].at[i] = 'C'
                        else:
                            grade[subject].at[i] = 'D'

                    else:
                        grade[subject].at[i] = 'E'
                else:
                    grade[subject].at[i] = 'F'

        grade_assess('语文')
        grade_assess('数学')
        grade_assess('英语')
        grade_assess('政治')
        grade_assess('历史')
        grade_assess('地理')

        final_grade = pd.concat([result_single, result_double, shoot_df, contribution_df, grade],
                                keys=['单有效', '双有效', '贡献率', '命中率', '等级'])

        return result_final_arts, final_grade, unmatched_arts

    def goodscore_science(self, chn, math, eng, pol, his, geo, total):
        """
        计算理科各科各班上单有效和双有效分人数
        """
        single_chn_science = self.df_science[self.df_science['语文'] >= chn].groupby(['班级'])['语文'].count()
        single_math_science = self.df_science[self.df_science['数学'] >= math].groupby(['班级'])['数学'].count()
        single_eng_science = self.df_science[self.df_science['英语'] >= eng].groupby(['班级'])['英语'].count()
        single_phys_science = self.df_science[self.df_science['物理'] >= pol].groupby(['班级'])['物理'].count()
        single_chem_science = self.df_science[self.df_science['化学'] >= his].groupby(['班级'])['化学'].count()
        single_bio_science = self.df_science[self.df_science['生物'] >= geo].groupby(['班级'])['生物'].count()
        single_total_science = self.df_science[self.df_science['总分'] >= total].groupby(['班级'])['总分'].count()

        name_num = self.df_science.groupby(['班级'])['姓名'].count()
        name_num.name = '参考人数'

        df2 = self.df_science[self.df_science['总分'] >= total]
        double_chn_science = df2[df2['语文'] >= chn].groupby(['班级'])['语文'].count()
        double_math_science = df2[df2['数学'] >= math].groupby(['班级'])['数学'].count()
        double_eng_science = df2[df2['英语'] >= eng].groupby(['班级'])['英语'].count()
        double_phys_science = df2[df2['物理'] >= pol].groupby(['班级'])['物理'].count()
        double_chem_science = df2[df2['化学'] >= his].groupby(['班级'])['化学'].count()
        double_bio_science = df2[df2['生物'] >= geo].groupby(['班级'])['生物'].count()
        double_total__science = df2[df2['总分'] >= total].groupby(['班级'])['总分'].count()

        goodscore_dict = {'参考人数': ' ', '语文': chn, '数学': math, '英语': eng, '物理': pol, '化学': his, '生物': geo, '总分': total}
        goodscore_df = pd.DataFrame(goodscore_dict, index=['有效分数'])

        result_single = pd.concat([name_num, single_chn_science, single_math_science, single_eng_science,
                                   single_phys_science, single_chem_science, single_bio_science, single_total_science],
                                  axis=1)
        result_double = pd.concat(
            [name_num, double_chn_science, double_math_science, double_eng_science, double_phys_science,
             double_chem_science, double_bio_science, double_total__science], axis=1)

        result_single.loc['理科'] = [result_single['参考人数'].sum(),
                                   result_single['语文'].sum(),
                                   result_single['数学'].sum(),
                                   result_single['英语'].sum(),
                                   result_single['物理'].sum(),
                                   result_single['化学'].sum(),
                                   result_single['生物'].sum(),
                                   result_single['总分'].sum()
                                   ]
        result_single['上线率'] = result_single['总分'] / result_single['参考人数']
        result_single['上线率'] = result_single['上线率'].apply(lambda x: format(x, '.2%'))
        result_double.loc['理科'] = [result_double['参考人数'].sum(),
                                   result_double['语文'].sum(),
                                   result_double['数学'].sum(),
                                   result_double['英语'].sum(),
                                   result_double['物理'].sum(),
                                   result_double['化学'].sum(),
                                   result_double['生物'].sum(),
                                   result_double['总分'].sum()]
        unmatched_dict = {'参考人数': name_num, '语文': single_total_science - double_chn_science,
                          '数学': single_total_science - double_math_science,
                          '英语': single_total_science - double_eng_science,
                          '物理': single_total_science - double_phys_science,
                          '化学': single_total_science - double_chem_science,
                          '生物': single_total_science - double_bio_science,
                          '总分': single_total_science - double_total__science}
        unmatched_df = pd.DataFrame(unmatched_dict)
        unmatched_df.loc['共计'] = [unmatched_df['参考人数'].sum(),
                                  unmatched_df['语文'].sum(),
                                  unmatched_df['数学'].sum(),
                                  unmatched_df['英语'].sum(),
                                  unmatched_df['物理'].sum(),
                                  unmatched_df['化学'].sum(),
                                  unmatched_df['生物'].sum(),
                                  unmatched_df['总分'].sum()]

        result_final_science = pd.concat([goodscore_df, result_single, result_double, unmatched_df], axis=0,
                                         keys=['有效分数', '单有效', '双有效', '错位数'])

        df_chn = df2.loc[:, ['班级', '姓名', '语文']].loc[df2['语文'] < chn].sort_values(by=['班级', '语文'],
                                                                                 ascending=[True, False]).reset_index(
            drop=True)
        df_math = df2.loc[:, ['班级', '姓名', '数学']].loc[df2['数学'] < math].sort_values(by=['班级', '数学'],
                                                                                   ascending=[True, False]).reset_index(
            drop=True)
        df_eng = df2.loc[:, ['班级', '姓名', '英语']].loc[df2['英语'] < eng].sort_values(by=['班级', '英语'],
                                                                                 ascending=[True, False]).reset_index(
            drop=True)
        df_pol = df2.loc[:, ['班级', '姓名', '物理']].loc[df2['物理'] < pol].sort_values(by=['班级', '物理'],
                                                                                 ascending=[True, False]).reset_index(
            drop=True)
        df_his = df2.loc[:, ['班级', '姓名', '化学']].loc[df2['化学'] < his].sort_values(by=['班级', '化学'],
                                                                                 ascending=[True, False]).reset_index(
            drop=True)
        df_geo = df2.loc[:, ['班级', '姓名', '生物']].loc[df2['生物'] < geo].sort_values(by=['班级', '生物'],
                                                                                 ascending=[True, False]).reset_index(
            drop=True)
        unmatched_science = pd.concat([df_chn, df_math, df_eng, df_pol, df_his, df_geo], axis=1)
        shoot_dict = {'语文': result_double['语文'] / result_single['语文'],
                      '数学': result_double['数学'] / result_single['数学'],
                      '英语': result_double['英语'] / result_single['英语'],
                      '物理': result_double['物理'] / result_single['物理'],
                      '化学': result_double['化学'] / result_single['化学'],
                      '生物': result_double['生物'] / result_single['生物'],
                      '上线数80%': result_single['总分'] * 0.8}
        shoot_df = pd.DataFrame(shoot_dict)
        contribution_dict = {'语文': result_double['语文'] / result_double['总分'],
                             '数学': result_double['数学'] / result_double['总分'],
                             '英语': result_double['英语'] / result_double['总分'],
                             '物理': result_double['物理'] / result_double['总分'],
                             '化学': result_double['化学'] / result_double['总分'],
                             '生物': result_double['生物'] / result_double['总分'],
                             '上线数80%': result_double['总分'] * 0.8}
        contribution_df = pd.DataFrame(contribution_dict)
        result_single.fillna(0, inplace=True)
        result_double.fillna(0, inplace=True)
        shoot_df.fillna(0, inplace=True)
        contribution_df.fillna(0, inplace=True)
        grade = pd.DataFrame(columns=['语文', '数学', '英语', '物理', '化学', '生物'], index=shoot_df.index)

        def grade_assess(subject):
            for i in shoot_df.index:
                if result_single['总分'].at[i] != 0:
                    if (result_single[subject].at[i]) >= (result_single['总分'].at[i]) * 0.8:
                        if (contribution_df[subject].at[i] >= 0.7) & (shoot_df[subject].at[i] >= 0.6):
                            grade[subject].at[i] = 'A'
                        elif (contribution_df[subject].at[i] >= 0.7) & (shoot_df[subject].at[i] < 0.6):
                            grade[subject].at[i] = 'B'
                        elif (contribution_df[subject].at[i] < 0.7) & (shoot_df[subject].at[i] >= 0.6):
                            grade[subject].at[i] = 'C'
                        else:
                            grade[subject].at[i] = 'D'

                    else:
                        grade[subject].at[i] = 'E'
                else:
                    grade[subject].at[i] = 'F'

        grade_assess('语文')
        grade_assess('数学')
        grade_assess('英语')
        grade_assess('物理')
        grade_assess('化学')
        grade_assess('生物')

        final_grade = pd.concat([result_single, result_double, shoot_df, contribution_df, grade],
                                keys=['单有效', '双有效', '贡献率', '命中率', '等级'])

        return result_final_science, final_grade, unmatched_science

    def line_betweens(self, total=None, total_science=None):
        line_condition = (self.df_arts['总分'] >= total - 20) & (self.df_arts['总分'] <= total + 20)
        line_condition_science = (self.df_science['总分'] >= total_science - 20) & (
                self.df_science['总分'] <= total_science + 20)
        df_line_arts = self.df_arts.loc[line_condition, :]
        df_line_science = self.df_science.loc[line_condition_science, :]
        writer = pd.ExcelWriter(r'D:\成绩统计结果\本次考试踩线生分班名单.xlsx')
        class_num = list(df_line_arts['班级'].drop_duplicates())
        class_num_science = list(df_line_science['班级'].drop_duplicates())
        for i in class_num:
            class_name = df_line_arts[df_line_arts['班级'] == i].reset_index(drop=True)
            class_name['序号'] = [k + 1 for k in class_name.index]
            class_name = class_name.loc[:, ['序号', '姓名', '班级', '语文', '数学', '英语',
                                            '政治', '历史', '地理', '总分', '排名']]
            class_name.to_excel(writer, sheet_name=i, index=False)

        for i in class_num_science:
            class_name = df_line_science[df_line_science['班级'] == i].reset_index(drop=True)
            class_name['序号'] = [k + 1 for k in class_name.index]
            class_name = class_name.loc[:, ['序号', '姓名', '班级', '语文', '数学', '英语',
                                            '物理', '化学', '生物', '总分', '排名']]
            class_name.to_excel(writer, sheet_name=i, index=False)

        writer.close()

    def class_divided(self):
        """
        计算获得文理科各班成绩表
        :return:
        """
        self.class_rank()
        class_arts = list(self.df_arts['班级'].drop_duplicates())
        class_science = list(self.df_science['班级'].drop_duplicates())
        writer = pd.ExcelWriter(r'D:\成绩统计结果\本次考试分班成绩表.xlsx')
        for i in class_arts:
            class_data = self.df_arts[self.df_arts['班级'] == i].reset_index(drop=True)
            class_data['序号'] = [k + 1 for k in class_data.index]
            class_data['综合'] = class_data['政治'] + class_data['历史'] + class_data['地理']
            class_data = class_data.loc[:, ['序号', '姓名', '班级', '语文', '数学', '英语', '综合',
                                            '政治', '历史', '地理', '总分', '排名']]
            class_data.to_excel(writer, sheet_name=i, index=False)
        # writer.save()
        for i in class_science:
            class_data = self.df_science[self.df_science['班级'] == i].reset_index(drop=True)
            class_data['序号'] = [k + 1 for k in class_data.index]
            class_data['综合'] = class_data['物理'] + class_data['化学'] + class_data['生物']
            class_data = class_data.loc[:, ['序号', '姓名', '班级', '语文', '数学', '英语', '综合',
                                            '物理', '化学', '生物', '总分', '排名']]
            class_data.to_excel(writer, sheet_name=i, index=False)
        writer.close()

    def class_rank(self):
        """
        计算文理科学生总分排名
        :return:
        """
        self.df_arts['排名'] = self.df_arts['总分'].rank(method='min', ascending=False)
        self.df_arts['排名'] = self.df_arts['排名'].apply(lambda x: format(int(x)))
        self.df_arts.sort_values(by='总分', ascending=False, inplace=True)
        self.df_science['排名'] = self.df_science['总分'].rank(method='min', ascending=False)
        self.df_science['排名'] = self.df_science['排名'].apply(lambda x: format(int(x)))
        self.df_science.sort_values(by='总分', ascending=False, inplace=True)

    def score_label(self):
        """
        计算打印考生个人成绩单
        """
        self.class_rank()
        exam_arts = self.df_arts.loc[:, ['班级', '姓名', '语文', '数学', '英语', '政治', '历史', '地理', '总分', '排名']]
        exam_science = self.df_science.loc[:, ['班级', '姓名', '语文', '数学', '英语', '物理', '化学', '生物', '总分', '排名']]
        exam_arts = exam_arts.sort_values(by=['班级', '总分'], ascending=[True, False])
        exam_arts.reset_index(drop=True, inplace=True)
        exam_science = exam_science.sort_values(by=['班级', '总分'], ascending=[True, False])
        exam_science.reset_index(drop=True, inplace=True)
        for i in exam_arts.index:
            exam_arts.loc[i + 0.5] = ['班级', '姓名', '语文', '数学', '英语', '政治', '历史', '地理', '总分', '排名']
            # df2.to_excel(f'{df2.iloc[0,0]}.xlsx',index=False)
        exam_arts.sort_index(inplace=True, ignore_index=True)
        for i in exam_science.index:
            exam_science.loc[i + 0.5] = ['班级', '姓名', '语文', '数学', '英语', '物理', '化学', '生物', '总分', '排名']
        exam_science.sort_index(inplace=True, ignore_index=True)
        with pd.ExcelWriter(r'D:\成绩统计结果\本次考试学生个人成绩单.xlsx') as writer:
            exam_arts.to_excel(writer, sheet_name='文科成绩单', index=False)
            exam_science.to_excel(writer, sheet_name='理科成绩单', index=False)
        print('successfully done')

    def av_combined(self):
        """把文科和理科平均分合成一张excel电子表"""
        arts, science = self.get_av()
        arts.to_excel('arts.xlsx')
        science.to_excel('science.xlsx')
        arts_av = pd.read_excel('arts.xlsx', header=None)
        science_av = pd.read_excel('science.xlsx', header=None)
        os.remove('arts.xlsx')
        os.remove('science.xlsx')
        av = arts_av.append(science_av)

        with pd.ExcelWriter(r'D:\成绩统计结果\文理科平均分总表.xlsx')as writer:
            av.to_excel(writer, sheet_name='average', index=False, header=False, float_format='%.2f')

    def write_open(self, f):
        f.to_excel('f.xlsx')
        new_f = pd.read_excel('f.xlsx', header=None)
        os.remove('f.xlsx')
        return new_f

    def make_directory(self):
        if os.path.exists('D:\\成绩统计结果') == False:
            os.makedirs('D:\\成绩统计结果')

    def arts_science_combined(self):
        arts_av, science_av = self.get_av()
        arts, grades_arts, unmatched_arts = self.goodscore_arts(*ScoreAnalysis.arts_scores)
        science, grades_science, unmatched_science = self.goodscore_science(*ScoreAnalysis.science_scores)
        arts_av = self.write_open(arts_av)
        science_av = self.write_open(science_av)
        arts = self.write_open(arts)
        science = self.write_open(science)
        grades_arts = self.write_open(grades_arts)
        grades_science = self.write_open(grades_science)
        arts_science_av = pd.concat([arts_av, science_av])
        arts_science_goodscores = pd.concat([arts, science], ignore_index=True)
        arts_science_grade = pd.concat([grades_arts, grades_science], ignore_index=True)
        with pd.ExcelWriter(r'D:\成绩统计结果\文理有效分统计分析.xlsx') as writer:
            arts_science_av.to_excel(writer, sheet_name='文理平均分', float_format='%.2f', index=False)
            arts_science_goodscores.to_excel(writer, sheet_name='文理有效分', index=False)
            arts_science_grade.to_excel(writer, sheet_name='文理等级评定', float_format='%.2f', index=False)

    def arts_science_combined_school(self, goodtotal_arts=None, goodtotal_science=None):
        chn, math, eng, pol, his, geo, total = self.get_goodscores_arts(goodtotal_arts)
        chn_science, math_science, eng_science, phys, chem, bio, total_science = self.get_goodscores_science(
            goodtotal_science)
        arts_av, science_av = self.get_av()
        arts, grades_arts, unmatched_arts = self.goodscore_arts(chn, math, eng, pol, his, geo, total)
        science, grades_science, unmatched_science = self.goodscore_science(chn_science, math_science, eng_science,
                                                                            phys, chem, bio,
                                                                            total_science)
        arts_av = self.write_open(arts_av)
        science_av = self.write_open(science_av)
        arts = self.write_open(arts)
        science = self.write_open(science)
        grades_arts = self.write_open(grades_arts)
        grades_science = self.write_open(grades_science)
        arts_science_av = pd.concat([arts_av, science_av])
        arts_science_goodscores = pd.concat([arts, science])
        arts_science_grade = pd.concat([grades_arts, grades_science])
        with pd.ExcelWriter(r'D:\成绩统计结果\文理有效分统计分析.xlsx') as writer:
            arts_science_av.to_excel(writer, sheet_name='文理平均分', float_format='%.2f', index=False)
            arts_science_goodscores.to_excel(writer, sheet_name='文理有效分', index=False)
            arts_science_grade.to_excel(writer, sheet_name='文理等级评定', float_format='%.2f', index=False)

    def exam_room_info(self):
        """
        计算生成文理科各考室学生名单
        :return:
        """
        self.df_arts.sort_values(by='总分', ascending=False, inplace=True)
        self.df_science.sort_values(by='总分', ascending=False, inplace=True)
        if len(self.df_arts) % 30 != 0:
            room_numbers = [f'文科第{str(i + 1)}考室' for i in list(range(len(self.df_arts) // 30 + 1))]
        else:
            room_numbers = [f'文科第{str(i + 1)}考室' for i in list(range(len(self.df_arts) // 30))]
        if len(self.df_science) % 30 != 0:
            room_numbers_science = [f'理科第{str(i + 1)}考室' for i in list(range(len(self.df_science) // 30 + 1))]
        else:
            room_numbers_science = [f'理科第{str(i + 1)}考室' for i in list(range(len(self.df_science) // 30))]
        print(room_numbers)
        print(room_numbers_science)
        df_arts = self.df_arts.copy()
        df_science = self.df_science.copy()
        df_arts['考室号'] = ''
        df_arts['座位号'] = ''
        df_arts = df_arts.loc[:, ['班级', '姓名', '考号', '考室号', '座位号']]
        df_arts.reset_index(drop=True, inplace=True)
        df_science['考室号'] = ''
        df_science['座位号'] = ''
        df_science = df_science.loc[:, ['班级', '姓名', '考号', '考室号', '座位号']]
        df_science.reset_index(drop=True, inplace=True)

        df_room_students = []
        df_room_students_science = []
        for idx, room_number in enumerate(room_numbers):
            begin = idx * 30
            end = begin + 30
            df_room_student = df_arts.iloc[begin:end]
            df_room_students.append((idx, room_number, df_room_student))
            # print(df_room_students)
        writer = pd.ExcelWriter(r'D:\成绩统计结果\文理科考室学生名单.xlsx')
        for idx, room_number, df_room_student in df_room_students:
            for i in df_room_student.index:
                df_room_student['考室号'].at[i] = room_number
                df_room_student['座位号'].at[i] = i + 1 if i < 30 else i - idx * 30 + 1
                df_room_student.to_excel(writer, sheet_name=room_number, index=False)
        for idx, room_number in enumerate(room_numbers_science):
            begin = idx * 30
            end = begin + 30
            df_room_student_science = df_science.iloc[begin:end]
            df_room_students_science.append((idx, room_number, df_room_student_science))
        for idx, room_number, df_room_student_science in df_room_students_science:
            for i in df_room_student_science.index:
                df_room_student_science['考室号'].at[i] = room_number
                df_room_student_science['座位号'].at[i] = i + 1 if i < 30 else i - idx * 30 + 1
                df_room_student_science.to_excel(writer, sheet_name=room_number, index=False)
        df_arts_general = df_arts.sort_values(by=['班级', '考室号'], ascending=[True, True])
        df_science_general = df_science.sort_values(by=['班级', '考室号'], ascending=[True, True])
        df_arts_general.to_excel(writer, sheet_name='文科', index=False)
        df_science_general.to_excel(writer, sheet_name='理科', index=False)
        # 计算考生座签
        df_seat_arts = df_arts
        df_seat_science = df_science
        for i in df_seat_arts.index:
            df_seat_arts.loc[i + 0.5] = ['班级', '姓名', '考号', '考室号', '座位号']
            # df2.to_excel(f'{df2.iloc[0,0]}.xlsx',index=False)
        df_seat_arts.sort_index(inplace=True, ignore_index=True)
        for i in df_seat_science.index:
            df_seat_science.loc[i + 0.5] = ['班级', '姓名', '考号', '考室号', '座位号']
        df_seat_science.sort_index(inplace=True, ignore_index=True)
        df_seat_arts.to_excel(writer, sheet_name='文科座签', index=False)
        df_seat_science.to_excel(writer, sheet_name='理科座签', index=False)

        writer.close()
        print('successfully done')

    def combine_files(self, exam_record=r'D:\成绩统计结果\本次考试成绩统计.xlsx'):
        """
        计算区及市上考试相关数据，平均分，有效分，分班成绩表等。
        :param exam_record:
        :return:
        """
        av_arts, av_science = self.get_av()
        self.class_divided()
        arts, grades_arts, unmatched_arts = self.goodscore_arts(*ScoreAnalysis.arts_scores)
        science, grades_science, unmatched_science = self.goodscore_science(*ScoreAnalysis.science_scores)
        self.line_betweens(total=ScoreAnalysis.arts_scores[-1], total_science=ScoreAnalysis.science_scores[-1])
        with pd.ExcelWriter(exam_record) as writer:
            self.df_arts.to_excel(writer, sheet_name='文科总表')
            self.df_science.to_excel(writer, sheet_name='理科总表')
            av_arts.to_excel(writer, sheet_name='文科平均分', float_format='%.2f')
            av_science.to_excel(writer, sheet_name='理科平均分', float_format='%.2f')
            arts.to_excel(writer, sheet_name='文科有效分')
            unmatched_arts.to_excel(writer, sheet_name='文科错位生', index=False)
            science.to_excel(writer, sheet_name='理科有效分')
            unmatched_science.to_excel(writer, sheet_name='理科错位生', index=False)
            grades_arts.to_excel(writer, sheet_name='文科贡献率', float_format='%.2f')
            grades_science.to_excel(writer, sheet_name='理科贡献率', float_format='%.2f')
        print('successfully done')

    def combine_files_school(self, exam_record=r'D:\成绩统计结果\本次考试成绩统计.xlsx', goodtotal_arts=None, goodtotal_science=None):

        """
        计算学校考试相关数据，平均分，有效分，分班成绩表等。
        """

        chn, math, eng, pol, his, geo, total = self.get_goodscores_arts(goodtotal_arts)
        chn_science, math_science, eng_science, phys, chem, bio, total_science = self.get_goodscores_science(
            goodtotal_science)
        self.class_divided()
        self.line_betweens(total=total, total_science=total_science)
        av_arts, av_science = self.get_av()
        arts, grades_arts, unmatched_arts = self.goodscore_arts(chn, math, eng, pol, his, geo, total)
        science, grades_science, unmatched_science = self.goodscore_science(chn_science, math_science, eng_science,
                                                                            phys, chem, bio,
                                                                            total_science)
        with pd.ExcelWriter(exam_record) as writer:
            self.df_arts.to_excel(writer, sheet_name='文科总表')
            self.df_science.to_excel(writer, sheet_name='理科总表')
            av_arts.to_excel(writer, sheet_name='文科平均分', float_format='%.2f')
            av_science.to_excel(writer, sheet_name='理科平均分', float_format='%.2f')
            arts.to_excel(writer, sheet_name='文科有效分')
            unmatched_arts.to_excel(writer, sheet_name='文科错位生', index=False)
            science.to_excel(writer, sheet_name='理科有效分')
            unmatched_science.to_excel(writer, sheet_name='理科错位生', index=False)
            grades_arts.to_excel(writer, sheet_name='文科贡献率', float_format='%.2f')
            grades_science.to_excel(writer, sheet_name='理科贡献率', float_format='%.2f')
        print('successfully done')


def time_used(f):
    def wrapper(*args, **kwargs):
        t1 = time.time()
        res = f(*args, **kwargs)
        t2 = time.time()
        print(f'{f.__name__}耗时共计{t2 - t1:.2f}秒。')
        return res

    return wrapper


if __name__ == '__main__':
    # 计算获得考试统计数据
    start_time = time.perf_counter()
    exam = ScoreAnalysis(r'D:\年级管理数据\高2018级\高三下\二诊成绩\二诊成绩数据.xlsx')
    exam_room = ExamRoom(r'D:\年级管理数据\高2018级\高三下\高三考试考室安排名单.xlsx')
    ScoreAnalysis.arts_scores = [91, 73, 79, 56, 59, 56, 415]
    ScoreAnalysis.science_scores = [85, 79, 73, 44, 45, 49, 375]


    @time_used
    def scores_assistant():
        global arts_scores, science_scores
        while True:
            flag = eval(input(f'按键功能选择：1:年级考试成绩分析； 2:区级以上考试成绩分析；'
                              f' 3:生成考室安排表； 4:生成成绩单； 按其它数字键退出程序。请选择：'))
            if flag == 1:
                arts = eval(input('请输入本次考试文科上线总分：'))
                science = eval(input('请输入本次考试理科上线总分：'))
                exam.combine_files_school(goodtotal_arts=arts, goodtotal_science=science)
                print('成绩分析已完成，谢谢使用！')
                break
            elif flag == 2:
                # arts_scores = [int(i) for i in input('请输入文科有效分及上线总分，以空格隔开:').split()]
                # science_scores = [int(i) for i in input('请输入理科有效分及上线总分，以空格隔开:').split()]
                exam.combine_files()
                print('成绩分析已完成，谢谢使用！')
                break
            elif flag == 3:
                exam_room.exam_room_info()
                print('考室信息已生成，谢谢使用！')
                break
            elif flag == 4:
                exam.score_label()
                print('学生个人成绩单已生成，谢谢使用！')
                break
            else:
                break
        end_time = time.perf_counter()
        time_total = end_time - start_time
        print(f'程序运行结束，共计耗时{time_total:.2f}秒')


    print()
    print(f'{"欢迎使用高2021级考试成绩管理系统":*^80}')
    print()
    scores_assistant()
    print(f'{"谢谢使用，再见！":*^80}')
